package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.entity.Circlecomment;
import com.zzxz.practicaltrainingsys.entity.Circlefile;
import com.zzxz.practicaltrainingsys.entity.Circle;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface CircleMapper {

    // 获取帖子列表（带排序）
    @Select("<script>" +
            "SELECT c.*, " +
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_sex " +  // 获取学生性别
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_sex " +  // 获取教师性别
            "    WHEN c.circle_role = 'admin' THEN a.admin_sex " +       // 获取管理员性别
            "  END AS circle_sex, " +  // 映射到circle_sex字段
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_name " +
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_name " +
            "    WHEN c.circle_role = 'admin' THEN a.admin_name " +
            "  END AS student_name, " +
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_image " +
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_image " +
            "    WHEN c.circle_role = 'admin' THEN a.admin_image " +
            "  END AS student_image, " +
            "  CASE " +
            "    WHEN c.circle_role = 'teacher' THEN (SELECT COUNT(*) FROM project p WHERE p.teacher_id = t.teacher_id) " +
            "  END AS projectCount, " +
            "  a.admin_role, a.admin_email, " +
            "  CASE WHEN c.circle_good IS NULL OR c.circle_good = '' THEN 0 " +
            "       ELSE LENGTH(c.circle_good) - LENGTH(REPLACE(c.circle_good, ',', '')) + 1 END as likeCount " +
            "FROM circle c " +
            "LEFT JOIN student s ON c.circle_role = 'student' AND c.circlestu_id = s.student_id " +
            "LEFT JOIN teacher t ON c.circle_role = 'teacher' AND c.circlestu_id = t.teacher_id " +
            "LEFT JOIN adm a ON c.circle_role = 'admin' AND c.circlestu_id = a.admin_id " +
            "WHERE c.circle_state != '违规' " +
            "<if test='sort == \"hot\"'> ORDER BY likeCount DESC </if>" +
            "<if test='sort == \"latest\"'> ORDER BY c.circle_time DESC </if>" +
            "</script>")
    List<Circle> getCircleList(@Param("sort") String sort);

    // 获取单个帖子详情
    @Select("SELECT c.*, " +
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_sex " +  // 获取学生性别
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_sex " +  // 获取教师性别
            "    WHEN c.circle_role = 'admin' THEN a.admin_sex " +      // 获取管理员性别
            "  END AS circle_sex, " +  // 映射到circle_sex字段
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_name " +
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_name " +
            "    WHEN c.circle_role = 'admin' THEN a.admin_name " +
            "  END AS student_name, " +
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_image " +
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_image " +
            "    WHEN c.circle_role = 'admin' THEN a.admin_image " +
            "  END AS student_image, " +
            "  CASE " +
            "    WHEN c.circle_role = 'teacher' THEN (SELECT COUNT(*) FROM project p WHERE p.teacher_id = t.teacher_id) " +
            "    ELSE NULL " +
            "  END AS project_count, " +
            "  a.admin_role, a.admin_email, " +
            "  CASE WHEN c.circle_good IS NULL OR c.circle_good = '' THEN 0 " +
            "       ELSE LENGTH(c.circle_good) - LENGTH(REPLACE(c.circle_good, ',', '')) + 1 END as likeCount " +
            "FROM circle c " +
            "LEFT JOIN student s ON c.circle_role = 'student' AND c.circlestu_id = s.student_id " +
            "LEFT JOIN teacher t ON c.circle_role = 'teacher' AND c.circlestu_id = t.teacher_id " +
            "LEFT JOIN adm a ON c.circle_role = 'admin' AND c.circlestu_id = a.admin_id " +  // 修正表名为admin
            "WHERE c.circle_id = #{circleId}")
    Circle getCircleById(Integer circleId);

    // 新增帖子
    @Insert("INSERT INTO circle (circlestu_id,circle_role, cycle_type, problem_type, circle_content, " +
            "circle_time, circle_key, circle_title,circle_state,circle_edit) " +
            "VALUES (#{circlestu_id},#{circle_role}, #{cycle_type}, #{problem_type}, #{circle_content}, " +
            "NOW(), #{circle_key}, #{circle_title},#{circle_state}, '发布于')")
    @Options(useGeneratedKeys = true, keyProperty = "circle_id")
    int insertCircle(Circle circle);

    // 插入文件记录
    @Insert("INSERT INTO circlefile (circle_id, file_name,file_type, file_url, create_date, file_size) " +
            "VALUES (#{circle_id}, #{file_name},#{file_type}, #{file_url}, NOW(), #{file_size})")
    int insertCirclefile(Circlefile circlefile);

    // 根据帖子ID获取文件
    @Select("SELECT * FROM circlefile WHERE circle_id = #{circleId}")
    List<Circlefile> getFilesByCircleId(Integer circleId);

    // 点赞
    @Update("UPDATE circle SET circle_good = #{circle_good} WHERE circle_id = #{circle_id}")
    int likeCircle(@Param("circle_good") String circle_good, @Param("circle_id") Integer circle_id);

    // 插入评论
    @Insert("INSERT INTO circlecomment (circle_id,recomment_id, commentstu_id, comments, comment_time) " +
            "VALUES (#{circle_id},#{recomment_id}, #{commentstu_id}, #{comments}, NOW())")
    int insertComment(Circlecomment comment);

    // 获取帖子的评论树
    @Select("SELECT cc.*, " +
            // 评论者姓名
            "CASE " +
            "  WHEN cc.commentstu_id REGEXP '^[0-9]+$' THEN s.student_name " +
            "  WHEN cc.commentstu_id LIKE 't%' THEN t.teacher_name " +
            "  WHEN cc.commentstu_id LIKE 'a%' THEN a.admin_name " +
            "END AS commenter_name, " +
            // 评论者头像
            "CASE " +
            "  WHEN cc.commentstu_id REGEXP '^[0-9]+$' THEN s.student_image " +
            "  WHEN cc.commentstu_id LIKE 't%' THEN t.teacher_image " +
            "  WHEN cc.commentstu_id LIKE 'a%' THEN a.admin_image " +
            "END AS commenter_image, " +
            // 评论者身份
            "CASE " +
            "  WHEN cc.commentstu_id REGEXP '^[0-9]+$' THEN '学生' " +
            "  WHEN cc.commentstu_id LIKE 't%' THEN '教师' " +
            "  WHEN cc.commentstu_id LIKE 'a%' THEN a.admin_role " +
            "END AS identity, " +
            // 评论者性别
            "CASE " +
            "  WHEN cc.commentstu_id REGEXP '^[0-9]+$' THEN s.student_sex " +
            "  WHEN cc.commentstu_id LIKE 't%' THEN t.teacher_sex " +
            "  WHEN cc.commentstu_id LIKE 'a%' THEN a.admin_sex " +
            "END AS comment_sex, " +  // 命名为comment_sex
            // 学生所属机构信息
            "sch.school_name AS student_school, " +
            "col.college_name AS student_college, " +
            "m.major_name AS student_major, " +
            "g.grade_name AS student_grade, " +
            // 回复对象姓名
            "CASE " +
            "  WHEN rc.commentstu_id REGEXP '^[0-9]+$' THEN rs.student_name " +
            "  WHEN rc.commentstu_id LIKE 't%' THEN rt.teacher_name " +
            "  WHEN rc.commentstu_id LIKE 'a%' THEN ra.admin_name " +
            "END AS reply_to " +
            "FROM circlecomment cc " +
            // 评论者表连接
            "LEFT JOIN student s " +
            "  ON cc.commentstu_id REGEXP '^[0-9]+$' " +
            "  AND s.student_id = CAST(cc.commentstu_id AS UNSIGNED) " +
            "LEFT JOIN teacher t " +
            "  ON cc.commentstu_id LIKE 't%' " +
            "  AND t.teacher_id = CAST(SUBSTRING(cc.commentstu_id, 2) AS UNSIGNED) " +
            "LEFT JOIN adm a " +
            "  ON cc.commentstu_id LIKE 'a%' " +
            "  AND a.admin_id = CAST(SUBSTRING(cc.commentstu_id, 2) AS UNSIGNED) " +
            // 学生所属机构表连接
            "LEFT JOIN school sch ON s.school_id = sch.school_id " +
            "LEFT JOIN college col ON s.college_id = col.college_id " +
            "LEFT JOIN major m ON s.major_id = m.major_id " +
            "LEFT JOIN grade g ON s.grade_id = g.grade_id " +
            // 回复对象表连接
            "LEFT JOIN circlecomment rc ON cc.recomment_id = rc.comment_id " +
            "LEFT JOIN student rs " +
            "  ON rc.commentstu_id REGEXP '^[0-9]+$' " +
            "  AND rs.student_id = CAST(rc.commentstu_id AS UNSIGNED) " +
            "LEFT JOIN teacher rt " +
            "  ON rc.commentstu_id LIKE 't%' " +
            "  AND rt.teacher_id = CAST(SUBSTRING(rc.commentstu_id, 2) AS UNSIGNED) " +
            "LEFT JOIN adm ra " +
            "  ON rc.commentstu_id LIKE 'a%' " +
            "  AND ra.admin_id = CAST(SUBSTRING(rc.commentstu_id, 2) AS UNSIGNED) " +
            "WHERE cc.circle_id = #{circleId} " +
            "ORDER BY cc.comment_time ASC")
    List<Circlecomment> getCommentsByCircleId(Integer circleId);

    //根据身份(circle_role)和账号(circlestu_id)查询本人帖子信息
    // 获取指定用户的帖子列表（带身份过滤和排序）
    @Select("<script>" +
            "SELECT c.*, " +
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_name " +
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_name " +
            "    WHEN c.circle_role = 'admin' THEN a.admin_name " +
            "  END AS student_name, " +
            "  CASE " +
            "    WHEN c.circle_role = 'student' THEN s.student_image " +
            "    WHEN c.circle_role = 'teacher' THEN t.teacher_image " +
            "    WHEN c.circle_role = 'admin' THEN a.admin_image " +
            "  END AS student_image, " +
            "  CASE " +
            "    WHEN c.circle_role = 'teacher' THEN (SELECT COUNT(*) FROM project p WHERE p.teacher_id = t.teacher_id) " +
            "  END AS projectCount, " +
            "  CASE " +
            "    WHEN c.circle_role = 'teacher' THEN (SELECT COUNT(*) FROM project p WHERE p.teacher_id = t.teacher_id) " +
            "  END AS project_count, " +
            "  a.admin_role, a.admin_email, " +
            "  CASE WHEN c.circle_good IS NULL OR c.circle_good = '' THEN 0 " +
            "       ELSE LENGTH(c.circle_good) - LENGTH(REPLACE(c.circle_good, ',', '')) + 1 END as likeCount " +
            "FROM circle c " +
            "LEFT JOIN student s ON c.circle_role = 'student' AND c.circlestu_id = s.student_id " +
            "LEFT JOIN teacher t ON c.circle_role = 'teacher' AND c.circlestu_id = t.teacher_id " +
            "LEFT JOIN adm a ON c.circle_role = 'admin' AND c.circlestu_id = a.admin_id " +
            "WHERE c.circle_state != '违规' " +
            "<if test='role != null'>" +
            "  AND c.circle_role = #{role}" +
            "</if>" +
            "<if test='circlestu_id != null'>" +
            "  AND c.circlestu_id = #{circlestu_id}" +
            "</if>" +
            "<choose>" +
            "  <when test='sort == \"hot\"'> ORDER BY likeCount DESC </when>" +
            "  <when test='sort == \"latest\"'> ORDER BY c.circle_time DESC </when>" +
            "  <otherwise> ORDER BY c.circle_time DESC </otherwise>" +
            "</choose>" +
            "</script>")
    List<Circle> getCircleByRoleAndId(
            @Param("circlestu_id") Integer circlestu_id,
            @Param("role") String role,
            @Param("sort") String sort
    );

    // 更新帖子
    @Update("UPDATE circle SET " +
            "circle_title = #{circle_title}, " +
            "cycle_type = #{cycle_type}, " +
            "problem_type = #{problem_type}, " +
            "circle_content = #{circle_content}, " +
            "circle_key = #{circle_key}, " +
            "circle_time = NOW()," +
            "circle_edit = '编辑于'"+
            "WHERE circle_id = #{circle_id}")
    int updateCircle(Circle circle);

    // 根据ID获取文件
    @Select("SELECT * FROM circlefile WHERE file_id = #{fileId}")
    Circlefile getFileById(Integer fileId);

    // 删除文件
    @Delete("DELETE FROM circlefile WHERE file_id = #{fileId}")
    int deleteFile(Integer fileId);

    // 删除帖子关联文件
    @Delete("DELETE FROM circlefile WHERE circle_id = #{circleId}")
    int deleteCircleFiles(Integer circleId);

    // 删除帖子关联评论
    @Delete("DELETE FROM circlecomment WHERE circle_id = #{circleId}")
    int deleteComments(Integer circleId);

    // 删除帖子
    @Delete("DELETE FROM circle WHERE circle_id = #{circleId}")
    int deleteCircle(Integer circleId);

    // 根据父评论ID获取子评论
    @Select("SELECT * FROM circlecomment WHERE recomment_id = #{parentId}")
    List<Circlecomment> getCommentsByParentId(Integer parentId);

    // 根据ID删除评论
    @Delete("DELETE FROM circlecomment WHERE comment_id = #{commentId}")
    int deleteCommentById(Integer commentId);

    // 获取点赞用户信息
    @Select("<script>" +
            "SELECT " +
            "  CASE " +
            "    WHEN user_id REGEXP '^[0-9]+$' THEN s.student_name " +
            "    WHEN user_id LIKE 't%' THEN t.teacher_name " +
            "    WHEN user_id LIKE 'a%' THEN a.admin_name " +
            "  END AS name, " +
            "  CASE " +
            "    WHEN user_id REGEXP '^[0-9]+$' THEN '学生' " +
            "    WHEN user_id LIKE 't%' THEN '教师' " +
            "    WHEN user_id LIKE 'a%' THEN a.admin_role " +
            "  END AS role, " +
            "  CASE " +  // 头像字段
            "    WHEN user_id REGEXP '^[0-9]+$' THEN s.student_image " +
            "    WHEN user_id LIKE 't%' THEN t.teacher_image " +
            "    WHEN user_id LIKE 'a%' THEN a.admin_image " +
            "  END AS avatar, " +
            "  CASE " +  // 新增性别字段
            "    WHEN user_id REGEXP '^[0-9]+$' THEN s.student_sex " +
            "    WHEN user_id LIKE 't%' THEN t.teacher_sex " +
            "    WHEN user_id LIKE 'a%' THEN a.admin_sex " +
            "  END AS gender " +  // 性别字段别名为gender
            "FROM ( " +
            "  SELECT TRIM(user_id) AS user_id " +
            "  FROM ( " +
            "    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(#{userIds}, ',', numbers.n), ',', -1) AS user_id " +
            "    FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 " +
            "          UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 " +
            "          UNION ALL SELECT 9 UNION ALL SELECT 10) numbers " +
            "    WHERE CHAR_LENGTH(#{userIds}) - CHAR_LENGTH(REPLACE(#{userIds}, ',', '')) >= numbers.n - 1 " +
            "  ) split_users " +
            ") users " +
            "LEFT JOIN student s ON users.user_id REGEXP '^[0-9]+$' AND s.student_id = CAST(users.user_id AS UNSIGNED) " +
            "LEFT JOIN teacher t ON users.user_id LIKE 't%' AND t.teacher_id = CAST(SUBSTRING(users.user_id, 2) AS UNSIGNED) " +
            "LEFT JOIN adm a ON users.user_id LIKE 'a%' AND a.admin_id = CAST(SUBSTRING(users.user_id, 2) AS UNSIGNED) " +
            "</script>")
    List<Map<String, String>> getLikedUsers(@Param("userIds") String userIds);


    // 递归删除评论
    @Delete("DELETE FROM circlecomment WHERE comment_id IN (" +
            "  WITH RECURSIVE comment_tree AS (" +
            "    SELECT comment_id FROM circlecomment WHERE comment_id = #{commentId} " +
            "    UNION ALL " +
            "    SELECT c.comment_id FROM circlecomment c " +
            "    JOIN comment_tree ct ON c.recomment_id = ct.comment_id" +
            "  ) SELECT comment_id FROM comment_tree" +
            ")")
    int deleteCommentRecursive(@Param("commentId") Integer commentId);
}